﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL.Entity;

namespace ECS.DAL.Component
{
    public class WeightSlipComponent : BaseSQLModel
    {
         #region private properties
        private const string SQL_QUERY = " ";

        private const string SQL_PROC_QUERY = " ";

        private const string SQL_PROC_GET_LAST_SLIP_NO = "usp_get_lastslip ";

        private const string SQL_PARAM_SEARCH_SETUP_ID = " ";


        private const string SQL_INSERT = " ";

        private const string SQL_PROC_INSERT = "usp_InsertWeightSlip @AppId, @MawbId, @ScaleId, @StationId, @SlipNo,  " +
                                    " @Device, @UserId "; 

        private const string SQL_UPDATE = " ";

        private const string SQL_PROC_UPDATE = " ";

        private const string SQL_DELETE = " ";


        #endregion

        #region public properties
        /// <summary>
        /// Error result
        /// </summary>
        public ErrorInfo ErrorMessage { set; get; }
        #endregion

        #region constructor
        public WeightSlipComponent()
        {
            ErrorMessage = new ErrorInfo();
        }
        #endregion

        #region private methods
        
        #endregion

        #region public mehods
        public string GetLastSlipNo()
        {
            string lastSlipNo = string.Empty;
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append(SQL_PROC_GET_LAST_SLIP_NO);

                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    lastSlipNo = GetString(0, reader,"lastslip");
                }
            }
            return lastSlipNo;
        }
        /// <summary>
        /// get Eics setup data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<ObjectContext> Search(ObjectContext parameter)
        {
            WeightSlip param = (WeightSlip)parameter;
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            //using (Entities entities = new Entities())
            //{
            //    StringBuilder sqlString = new StringBuilder();
            //    sqlString.Append(SQL_PROC_QUERY);
            //    //sqlString.Append(SQL_QUERY);

            //    //if (!string.IsNullOrEmpty(param.SetupId))
            //    //{
            //    //    sqlString.Append(SQL_PARAM_SEARCH_SETUP_ID);
            //    //    sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(param.SetupId));
            //    //}
                
            //    entities.Open();
            //    Console.WriteLine(sqlString.ToString());
            //    DbCommand cmd = entities.CreateCommand(sqlString.ToString());
            //    cmd.CommandType = System.Data.CommandType.StoredProcedure;
            //    cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
            //    cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
            //    cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
            //    DbDataReader reader = cmd.ExecuteReader();
            //    while (reader.Read())
            //    {
            //        EicsSetup data = new EicsSetup();
            //        data.SetupId = base.GetString(0, reader);
            //        data.StationId = base.GetString(1, reader);
            //        data.SetupType = base.GetString(2, reader);
            //        data.SettingName = base.GetString(3, reader);
            //        data.ValueString = base.GetString(4, reader);
            //        data.ValueNumber  = (float?)base.GetDouble(5, reader);
            //        data.ValueDate = (DateTime?)base.GetDate(6, reader);
            //        data.CreateDate = (DateTime)base.GetDate(7, reader);
            //        data.CreateBy= base.GetString(8, reader);
            //        data.NotChangeObject();
            //        items.Add(data);
            //    }
            //}
            return items;
        }

        public override string GetInsertSQL(ObjectContext param)
        {
            /*@AppId, @MawbId, @ScaleId, @StationId, @SlipNo,  " +
                                    " @Device, @UserId ";  */
            WeightSlip parameter = (WeightSlip)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_INSERT);
            //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            //sqlString.Replace("@ScaleId", BaseSQLModel.SqlWithQoute(parameter.ScaleId));
            //sqlString.Replace("@StationId", BaseSQLModel.SqlWithQoute(parameter.StationId));
            //sqlString.Replace("@SlipNo", BaseSQLModel.SqlWithQoute(parameter.SlipNo));
            //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetUpdateSQL(ObjectContext param)
        {
            //store procedure
            //@in_vchApplicationID			VARCHAR(40), -- Required
            //@in_vchSetupID					VARCHAR(40) = NULL,  --Required
            //@in_vchType						VARCHAR(10),  -- Required
            //@in_vchStationID				VARCHAR(40),  -- Required
            //@in_vchSettingName				VARCHAR(50),  -- Required
            //@in_vchSettingValueString		VARCHAR(50), 
            //@in_fltSettingValueNumber		FLOAT, 
            //@in_dtSettingValueDateTime		DATETIME, 
            //@in_vchDevice				    NVARCHAR(50),  -- Required
            //@in_vchUserID				    NVARCHAR(20)  -- Required
            
            // sql command
            //"@AppId,  @setup_id, @type, @station_id, @setting_name, @setting_value_string, @setting_value_number, @setting_value_datetime, " +
            //"@Device, @UserId ";

            WeightSlip parameter = (WeightSlip)param;
            StringBuilder sqlString = new StringBuilder();
            ////sqlString.Append(SQL_UPDATE);
            //sqlString.Append(SQL_PROC_UPDATE);
            //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            //sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            //sqlString.Replace("@station_id", BaseSQLModel.SqlWithQoute(parameter.StationId));
            //sqlString.Replace("@type", BaseSQLModel.SqlWithQoute(parameter.SetupType));
            //sqlString.Replace("@setting_name", BaseSQLModel.SqlWithQoute(parameter.SettingName));
            //sqlString.Replace("@setting_value_string", BaseSQLModel.SqlWithQoute(parameter.ValueString));
            //sqlString.Replace("@setting_value_number", parameter.ValueNumber == null ? "NULL" : parameter.ValueNumber.ToString());
            //sqlString.Replace("@setting_value_datetime", parameter.ValueDate == null ? "NULL" : BaseSQLModel.SqlWithQoute(string.Format("{0:yyyy-MM-dd}", parameter.ValueDate)));
            ////sqlString.Replace("@create_date", BaseSQLModel.SqlWithQoute(parameter.CreateDate.ToString("yyyy-MM-dd")));
            ////sqlString.Replace("@create_by", BaseSQLModel.SqlWithQoute(parameter.CreateBy));
            //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetDeleteSQL(ObjectContext param)
        {
            WeightSlip parameter = (WeightSlip)param;
            StringBuilder sqlString = new StringBuilder();
            ////insert command
            //sqlString.Append(SQL_DELETE);
            //sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            return sqlString.ToString();
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {
            DataSet ds = new DataSet();
            WeightSlip parameter = (WeightSlip)param;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetInsertSQL(param));
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB_ID", parameter.MawbId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchScaleID", parameter.ScaleId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchStationID", parameter.StationId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipNumber", parameter.SlipNo));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                    DbDataAdapter adapter = entities.CreateDataAdapter(GetInsertSQL(param));
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds, "Result");
                    //entities.SetCommand(cmd);
                    //int m_iRecordChanged = entities.SaveChanges();
                    //if (m_iRecordChanged < 1)
                    if(ds.Tables[0].Rows.Count > 0)
                    {
                        ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                        ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                        ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                        ErrorMessage.ID = ds.Tables[0].Rows[0]["slip_id"].ToString();
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetUpdateSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("weight slip update unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetDeleteSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 0)
                    {
                        throw new Exception("weight slip delete unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        ///// <summary>
        ///// special case for store procedure to update data that return result as record
        ///// </summary>
        ///// <param name="items"></param>
        //public override void SaveChanges(List<ObjectContext> items)
        //{
        //    string sqlCmd = string.Empty;
        //    string textMessage = string.Empty;
        //    using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
        //    {
        //        using (Entities entities = new Entities())
        //        {
        //            entities.Open();
        //            DataSet ds = new DataSet();
        //            DbCommand cmd = entities.CreateCommand(sqlCmd);
                    
        //            DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
        //            adapter.SelectCommand = cmd;
        //            //cmd.CommandType = CommandType.StoredProcedure;
        //            foreach (ObjectContext item in items)
        //            {
        //                switch (item.GetObjectSate())
        //                {
        //                    case OBJECT_STATE.Insert:
        //                        sqlCmd = GetInsertSQL(item);
        //                        textMessage = "Insert ";
        //                        break;
        //                    case OBJECT_STATE.Update:
        //                        sqlCmd = GetUpdateSQL(item);
        //                        textMessage = "Update ";
        //                        break;
        //                    case OBJECT_STATE.Delete:
        //                        sqlCmd = GetDeleteSQL(item);
        //                        textMessage = "Delete ";
        //                        break;
        //                    default:
        //                        sqlCmd = string.Empty;
        //                        break;
        //                }
        //                if (!string.IsNullOrEmpty(sqlCmd))
        //                {
        //                    //DbCommand cmd = entities.CreateCommand(sqlCmd);
        //                    //cmd.CommandType = CommandType.StoredProcedure;
        //                    //entities.SetCommand(cmd);
        //                    //int m_iRecordChanged = entities.SaveChanges();
        //                    //int m_iRecordChanged = cmd.ExecuteNonQuery();
        //                    //if (m_iRecordChanged < 0)
        //                    //{
        //                    //    throw new Exception(string.Format("{0} transaction failed", textMessage));
        //                    //}
        //                    cmd.CommandText = sqlCmd;
        //                    adapter.Fill(ds);
        //                    if (ds.Tables[0].Rows.Count > 0)
        //                    {
        //                        if (!ds.Tables[0].Rows[0]["Status"].ToString().Equals("PASS"))
        //                        {
        //                            string errorMessage = string.Format("Status: {0} Error Code:{1}\r\nError:{2}", ds.Tables[0].Rows[0]["Status"].ToString(), ds.Tables[0].Rows[0]["ErrorCode"].ToString(), ds.Tables[0].Rows[0]["Message"].ToString());
        //                            throw new Exception(errorMessage);
        //                        }
        //                    }
        //                    item.NotChangeObject();
        //                }
        //            }
        //        }
        //        transaction.Complete();
        //    }
        //}
        #endregion
    }
}
